package cn.edu.nju.software.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;
import javax.sql.DataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;

import cn.edu.nju.software.dao.intf.BookDao;
import cn.edu.nju.software.bean.storage.Book;
import cn.edu.nju.software.util.Constants;

public class BookDaoImpl implements BookDao {

	private DataSource ds = null;

	public BookDaoImpl() {
		try {
			ds = JDBCUtil.getDataSource();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public int addBook(Book book) throws SQLException {
		// TODO Auto-generated method stub
		int result = -1;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			String sql = null;
			if (book.getAttachPath() == null) {
				sql = "INSERT INTO book (isbn, title, author, publisher, "
						+ "catg_id, abstract, price, in_stock, pic_path) "
						+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
			} else {
				sql = "INSERT INTO book (isbn, title, author, publisher, "
						+ "catg_id, abstract, price, in_stock, pic_path, attach) "
						+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			}
			pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, book.getIsbn());
			pstmt.setString(2, book.getTitle());
			pstmt.setString(3, book.getAuthor());
			pstmt.setString(4, book.getPublisher());
			pstmt.setInt(5, book.getCategoryID());
			pstmt.setString(6, book.getAbstractInfo());
			pstmt.setBigDecimal(7, book.getPrice());
			pstmt.setInt(8, book.getStock());
			pstmt.setString(9, book.getPicPath());
			if (book.getAttachPath() != null) {
				pstmt.setString(10, book.getAttachPath());
			}
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			while (rs.next()) {
				result = rs.getInt(1);
			}
			con.commit();
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			con.rollback();
			if (errorCode == JDBCUtil.VIOLATION_OF_UQ_CONSTRAINT) {
				result = -1;
			} else if (errorCode == JDBCUtil.VIOLATION_OF_FK_CONSTRAINT) {
				result = -2;
			} else {
				e.printStackTrace();
				throw e;
			}
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public List<Book> searchBooks(Map<Integer, Object> criteria)
			throws SQLException {
		// TODO Auto-generated method stub
		List<Book> list = new ArrayList<Book>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sql = new StringBuilder(
				"SELECT id, isbn, title, author, "
						+ "publisher, catg_id, abstract, price, sales, aval_date, "
						+ "in_stock, pic_path, attach FROM book WHERE ");
		int count = 1;
		if (criteria.containsKey(Constants.SEARCH_ISBN)) {
			if (count == 1) {
				sql.append("isbn = ?");
				++count;
			} else {
				sql.append(" AND isbn = ?");
			}
		}
		if (criteria.containsKey(Constants.SEARCH_CATEGORY)) {
			if (count == 1) {
				sql.append("catg_id = ?");
				++count;
			} else {
				sql.append(" AND catg_id = ?");
			}
		}
		if (criteria.containsKey(Constants.SEARCH_DATE)) {
			if (count == 1) {
				sql.append("aval_date >= ? AND aval_date <= ?");
				++count;
			} else {
				sql.append(" AND aval_date >= ? AND aval_date <= ?");
			}
		}
		if (criteria.containsKey(Constants.SEARCH_TITLE)) {
			if (count == 1) {
				sql.append("title LIKE ?");
				++count;
			} else {
				sql.append(" AND title LIKE ?");
			}
		}
		if (criteria.containsKey(Constants.SEARCH_AUTHOR)) {
			if (count == 1) {
				sql.append("author LIKE ?");
				++count;
			} else {
				sql.append(" AND author LIKE ?");
			}
		}
		if (criteria.containsKey(Constants.SEARCH_PUBLISHER)) {
			if (count == 1) {
				sql.append("publisher LIKE ?");
				++count;
			} else {
				sql.append(" AND publisher LIKE ?");
			}
		}

		sql.append(" ORDER BY title");

		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(sql.toString());
			count = 1;
			if (criteria.containsKey(Constants.SEARCH_ISBN)) {
				pstmt.setString(count,
						(String) criteria.get(Constants.SEARCH_ISBN));
				++count;
			}
			if (criteria.containsKey(Constants.SEARCH_CATEGORY)) {
				pstmt.setInt(count,
						(Integer) criteria.get(Constants.SEARCH_CATEGORY));
				++count;
			}
			if (criteria.containsKey(Constants.SEARCH_DATE)) {
				@SuppressWarnings("unchecked")
				List<Date> dates = (List<Date>) criteria
						.get(Constants.SEARCH_DATE);
				pstmt.setTimestamp(count, new Timestamp(dates.get(0).getTime()));
				++count;
				pstmt.setTimestamp(count, new Timestamp(dates.get(1).getTime()));
				++count;
			}
			if (criteria.containsKey(Constants.SEARCH_TITLE)) {
				pstmt.setString(count, JDBCUtil
						.assemblePattern((String) criteria
								.get(Constants.SEARCH_TITLE)));
				++count;
			}
			if (criteria.containsKey(Constants.SEARCH_AUTHOR)) {
				pstmt.setString(count, JDBCUtil
						.assemblePattern((String) criteria
								.get(Constants.SEARCH_AUTHOR)));
				++count;
			}
			if (criteria.containsKey(Constants.SEARCH_PUBLISHER)) {
				pstmt.setString(count, JDBCUtil
						.assemblePattern((String) criteria
								.get(Constants.SEARCH_PUBLISHER)));
				++count;
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Book(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getInt(6), rs.getString(7), rs.getBigDecimal(8), rs
						.getInt(9), rs.getTimestamp(10), rs.getInt(11), rs
						.getString(12), rs.getString(13)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public List<Book> getRecentBooks(Date date) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Book> list = new ArrayList<Book>();
		Timestamp time = new Timestamp(date.getTime());
		try {
			con = ds.getConnection();
			pstmt = con
					.prepareStatement("SELECT id, isbn, title, author, "
							+ "publisher, catg_id, abstract, price, sales, aval_date, "
							+ "in_stock, pic_path, attach FROM book WHERE aval_date >= ? "
							+ "ORDER BY aval_date DESC");
			pstmt.setTimestamp(1, time);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Book(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getInt(6), rs.getString(7), rs.getBigDecimal(8), rs
						.getInt(9), rs.getTimestamp(10), rs.getInt(11), rs
						.getString(12), rs.getString(13)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public List<Book> getHotBooks(Date date, int threshold) throws SQLException {
		// TODO Auto-generated method stub
		Timestamp time = new Timestamp(date.getTime());
		List<Book> list = new ArrayList<Book>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("SELECT b.id FROM book b, bmorder o, order_book ob "
							+ "WHERE b.id = ob.book_id AND o.id = ob.order_id AND o.conf_time >= ? "
							+ "GROUP BY b.id HAVING SUM(ob.quantity) >= ?");
			pstmt.setTimestamp(1, time);
			pstmt.setInt(2, threshold);
			List<Integer> idList = new ArrayList<Integer>();
			rs = pstmt.executeQuery();
			while (rs.next()) {
				idList.add(rs.getInt(1));
			}
			if (idList.size() == 0) {
				con.commit();
				return list;
			}
			rs.close();
			pstmt.close();
			StringBuilder sql = new StringBuilder(
					"SELECT id, isbn, title, author, "
							+ "publisher, catg_id, abstract, price, sales, aval_date, in_stock, "
							+ "pic_path, attach FROM book WHERE id in (");
			int j = idList.size() - 1;
			for (int i = 0; i < j; ++i) {
				sql.append("?, ");
			}
			sql.append("?) ORDER BY aval_date DESC");
			pstmt = con.prepareStatement(sql.toString());
			int count = 1;
			for (int i : idList) {
				pstmt.setInt(count, i);
				++count;
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Book(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getInt(6), rs.getString(7), rs.getBigDecimal(8), rs
						.getInt(9), rs.getTimestamp(10), rs.getInt(11), rs
						.getString(12), rs.getString(13)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public Book getBookById(int id) throws SQLException {
		// TODO Auto-generated method stub
		Book book = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("SELECT isbn, title, author, "
					+ "publisher, catg_id, abstract, price, sales, aval_date, "
					+ "in_stock, pic_path, attach FROM book WHERE id = ?");
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				book = new Book(id, rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6), rs.getBigDecimal(7), rs.getInt(8),
						rs.getTimestamp(9), rs.getInt(10), rs.getString(11),
						rs.getString(12));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return book;
	}

	@Override
	public int updateBook(Book book) throws SQLException {
		// TODO Auto-generated method stub
		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			pstmt = con
					.prepareStatement("UPDATE book SET title = ?, author = ?, "
							+ "publisher = ?, catg_id = ?, abstract = ?, price = ?, "
							+ "sales = ?, in_stock = ?, pic_path = ?, attach = ? WHERE id = ?");
			pstmt.setString(1, book.getTitle());
			pstmt.setString(2, book.getAuthor());
			pstmt.setString(3, book.getPublisher());
			pstmt.setInt(4, book.getCategoryID());
			pstmt.setString(5, book.getAbstractInfo());
			pstmt.setBigDecimal(6, book.getPrice());
			pstmt.setInt(7, book.getSales());
			pstmt.setInt(8, book.getStock());
			pstmt.setString(9, book.getPicPath());
			if (book.getAttachPath() == null) {
				pstmt.setNull(10, Types.VARCHAR);
			} else {
				pstmt.setString(10, book.getAttachPath());
			}
			pstmt.setInt(11, book.getBookID());
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			con.rollback();
			if (errorCode == JDBCUtil.VIOLATION_OF_FK_CONSTRAINT) {
				result = -1;
			} else {
				e.printStackTrace();
				throw e;
			}
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public boolean deleteBook(int bookID) throws SQLException {
		// TODO Auto-generated method stub
		boolean result = true;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("DELETE FROM book WHERE id = ?");
			pstmt.setInt(1, bookID);
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			con.rollback();
			if (errorCode == JDBCUtil.VIOLATION_OF_FK_CONSTRAINT) {
				result = false;
			} else {
				e.printStackTrace();
				throw e;
			}
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public List<Book> getAllBooks() throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Book> list = new ArrayList<Book>();
		try {
			con = ds.getConnection();
			pstmt = con
					.prepareStatement("SELECT id, isbn, title, author, "
							+ "publisher, catg_id, abstract, price, sales, aval_date, "
							+ "in_stock, pic_path, attach FROM book");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Book(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getInt(6), rs.getString(7), rs.getBigDecimal(8), rs
						.getInt(9), rs.getTimestamp(10), rs.getInt(11), rs
						.getString(12), rs.getString(13)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}
}
